<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_DICTIONARY_TYPE AS
  (SELECT T.ID,
    T.PARENT_ID,
    T.CODE,
    T.NAME,
    T.VALUE,
    T.ORDER_NUM,
    T.BEGIN_DATE,
    T.END_DATE ,
    row_number() over(order by T_DICTIONARY_TYPE.ORDER_NUM, T_DICTIONARY_TYPE.code, t.ORDER_NUM, t.CODE) rn,
    t_dictionary_type.code AS parentCode,
    t_dictionary_type.name AS parentName
  FROM C1_DICTIONARY_TYPE T
  LEFT JOIN C1_DICTIONARY_TYPE T_DICTIONARY_TYPE
  ON (Sysdate BETWEEN t_dictionary_type.begin_date AND t_dictionary_type.end_date
  AND T_DICTIONARY_TYPE.ID = t.parent_id)
  WHERE Sysdate BETWEEN t.begin_date AND t.end_date
<#if StringUtils.isNotBlank(parentCode)>
and (instr(T_DICTIONARY_TYPE.code, :parentCode) > 0 or instr(T_DICTIONARY_TYPE.name, :parentCode) > 0)
</#if>
<#if StringUtils.isNotBlank(parentId)>
and T.PARENT_ID = :parentId
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(t.code, :code) > 0 or instr(t.name, :code) > 0)
</#if>
  )
SELECT T.*,
  T.parentCode,
  T.parentName
FROM w_DICTIONARY_TYPE T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>

